!
!
Info TAFORA
!
Méthodes Conseil
!
Mon credo Oracle
!
Nouveautés
!
Expertise technique
!
Fiches Techniques
!
Séminaires
Technique
FAQ
Domaines
DBA
Tuning
PL/SQL
Divers
Varia
Liens
Contact
TAFORA
Radu
Mail
La requete qui tue ;-)
Un exemple de requete qui mesure les performances (?) d'une instance Oracle.
Quelqu'un m'a soumis une requête pour fliquer les perfs Oracle et elle me paraissait assez longue (comme je suis casse-cou, je ne l'ai pas vérifiée). 
select e.owner,  e.segment_type, e.segment_name, tablespace_name,  count(*)
from dba_extents e ,
(select * from v$bh ) b
where file# = e.file_id
and b.block# between e.block_id and e.block_id + e.blocks
group by e.owner, e.segment_type, e.segment_name, tablespace_name
Au bout de vingt minutes, je l'ai arrêtée, j'ai tracé la session (après un explain plan, et Oh, Horribile Visu) ça donne ceci :)
Pour ne pas vous obliger d'aller jusqu'en bas pour la conclusion, essayez de faire un explain pour les requetes avant de les lancer !

|         Rows Row Source Operation
| ------------ ---------------------------------------------------
|            0 SORT GROUP BY
|            0 .HASH JOIN OUTER
|            0 ..NESTED LOOPS
|            5 ...FIXED TABLE FULL X$BH I_VIEWCON1 (cr=0 pr=0 pw=0 time=0.00)
|            0 ...VIEW (cr=130859 pr=11228 pw=0 time=164.07)
|            0 ....UNION-ALL PARTITION (cr=130859 pr=11228 pw=0 time=164.07)
|            0 .....NESTED LOOPS (cr=68 pr=0 pw=0 time=0.00)
|            0 ......NESTED LOOPS (cr=68 pr=0 pw=0 time=0.00)
|            1 .......TABLE ACCESS BY INDEX ROWID OBJ# FILE$ (cr=6 pr=0 pw=0 time=0.00)
|            1 ........INDEX UNIQUE SCAN OBJ# I_FILE1 (cr=5 pr=0 pw=0 time=0.00)
|            0 .......TABLE ACCESS CLUSTER OBJ# UET$ (cr=62 pr=0 pw=0 time=0.00)
|           60 ........INDEX RANGE SCAN OBJ# I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=0.00)
|            0 ......VIEW
|            0 .......UNION-ALL PARTITION
|            0 ........NESTED LOOPS
|            0 .........NESTED LOOPS
|            0 ..........HASH JOIN
|            0 ...........NESTED LOOPS
|            0 ............NESTED LOOPS
|            0 .............TABLE ACCESS CLUSTER OBJ# TS$
|            0 ..............INDEX UNIQUE SCAN OBJ# I_TS#
|            0 .............TABLE ACCESS CLUSTER OBJ# SEG$
|            0 ..............INDEX RANGE SCAN OBJ# I_FILE#_BLOCK#
|            0 ............INDEX UNIQUE SCAN OBJ# I_FILE2
|            0 ...........VIEW
|            0 ............UNION-ALL
|            0 .............TABLE ACCESS FULL OBJ# TAB$
|            0 .............TABLE ACCESS FULL OBJ# TABPART$
|            0 .............TABLE ACCESS FULL OBJ# CLU$
|            0 .............TABLE ACCESS FULL OBJ# IND$
|            0 .............TABLE ACCESS FULL OBJ# INDPART$
|            0 .............TABLE ACCESS FULL OBJ# LOB$
|            0 .............TABLE ACCESS FULL OBJ# TABSUBPART$
|            0 .............TABLE ACCESS FULL OBJ# INDSUBPART$
|            0 .............TABLE ACCESS FULL OBJ# LOBFRAG$
|            0 ..........TABLE ACCESS BY INDEX ROWID OBJ# OBJ$
|            0 ...........INDEX UNIQUE SCAN OBJ# I_OBJ1
|            0 .........TABLE ACCESS CLUSTER OBJ# USER$
|            0 ..........INDEX UNIQUE SCAN OBJ# I_USER#
|            0 ........NESTED LOOPS
|            0 .........NESTED LOOPS
|            0 ..........NESTED LOOPS
|            0 ...........NESTED LOOPS
|            0 ............TABLE ACCESS FULL OBJ# UNDO$
|            0 ............INDEX UNIQUE SCAN OBJ# I_FILE2
|            0 ...........TABLE ACCESS CLUSTER OBJ# SEG$
|            0 ............INDEX UNIQUE SCAN OBJ# I_FILE#_BLOCK#
|            0 ..........TABLE ACCESS CLUSTER OBJ# TS$
|            0 ...........INDEX UNIQUE SCAN OBJ# I_TS#
|            0 .........TABLE ACCESS CLUSTER OBJ# USER$
|            0 ..........INDEX UNIQUE SCAN OBJ# I_USER#
|            0 ........NESTED LOOPS
|            0 .........NESTED LOOPS
|            0 ..........NESTED LOOPS
|            0 ...........TABLE ACCESS FULL OBJ# SEG$
|            0 ...........TABLE ACCESS CLUSTER OBJ# TS$
|            0 ............INDEX UNIQUE SCAN OBJ# I_TS#
|            0 ..........TABLE ACCESS BY INDEX ROWID OBJ# FILE$
|            0 ...........INDEX UNIQUE SCAN OBJ# I_FILE2
|            0 .........TABLE ACCESS CLUSTER OBJ# USER$
|            0 ..........INDEX UNIQUE SCAN OBJ# I_USER#
|            0 .....NESTED LOOPS (cr=130855 pr=11228 pw=0 time=164.07)
|        42799 ......NESTED LOOPS (cr=134887 pr=11504 pw=0 time=167.89)
|        12299 .......VIEW (cr=84479 pr=0 pw=0 time=4.19)
|        12299 ........UNION-ALL (cr=84479 pr=0 pw=0 time=4.11)
|        12247 .........NESTED LOOPS (cr=83493 pr=0 pw=0 time=3.85)
|        12247 ..........NESTED LOOPS (cr=58994 pr=0 pw=0 time=2.91)
|        12247 ...........NESTED LOOPS (cr=34488 pr=0 pw=0 time=2.18)
|        12247 ............HASH JOIN (cr=9989 pr=0 pw=0 time=1.45)
|        14925 .............NESTED LOOPS (cr=461 pr=0 pw=0 time=0.24)
|        14925 ..............TABLE ACCESS FULL OBJ# SEG$ (cr=455 pr=0 pw=0 time=0.08)
|        14925 ..............INDEX UNIQUE SCAN OBJ# I_FILE2 (cr=6 pr=0 pw=0 time=0.06)
|        13081 .............VIEW (cr=9528 pr=0 pw=0 time=0.77)
|        13081 ..............UNION-ALL (cr=9528 pr=0 pw=0 time=0.66)
|         4461 ...............TABLE ACCESS FULL OBJ# TAB$ (cr=2606 pr=0 pw=0 time=0.12)
|          220 ...............TABLE ACCESS FULL OBJ# TABPART$ (cr=16 pr=0 pw=0 time=0.00)
|           56 ...............TABLE ACCESS FULL OBJ# CLU$ (cr=2280 pr=0 pw=0 time=0.01)
|         6020 ...............TABLE ACCESS FULL OBJ# IND$ (cr=2280 pr=0 pw=0 time=0.14)
|          512 ...............TABLE ACCESS FULL OBJ# INDPART$ (cr=24 pr=0 pw=0 time=0.01)
|         1812 ...............TABLE ACCESS FULL OBJ# LOB$ (cr=2286 pr=0 pw=0 time=0.06)
|            0 ...............TABLE ACCESS FULL OBJ# TABSUBPART$ (cr=12 pr=0 pw=0 time=0.00)
|            0 ...............TABLE ACCESS FULL OBJ# INDSUBPART$ (cr=12 pr=0 pw=0 time=0.00)
|            0 ...............TABLE ACCESS FULL OBJ# LOBFRAG$ (cr=12 pr=0 pw=0 time=0.00)
|        12247 ............TABLE ACCESS CLUSTER OBJ# TS$ (cr=24500 pr=0 pw=0 time=0.56)
|        12247 .............INDEX UNIQUE SCAN OBJ# I_TS# (cr=6 pr=0 pw=0 time=0.13)
|        12247 ...........TABLE ACCESS BY INDEX ROWID OBJ# OBJ$ (cr=24506 pr=0 pw=0 time=0.57)
|        12247 ............INDEX UNIQUE SCAN OBJ# I_OBJ1 (cr=12253 pr=0 pw=0 time=0.27)
|        12247 ..........TABLE ACCESS CLUSTER OBJ# USER$ (cr=24500 pr=0 pw=0 time=0.77)
|        12247 ...........INDEX UNIQUE SCAN OBJ# I_USER# (cr=6 pr=0 pw=0 time=0.11)
|           44 .........NESTED LOOPS (cr=567 pr=0 pw=0 time=0.04)
|           44 ..........NESTED LOOPS (cr=474 pr=0 pw=0 time=0.04)
|           44 ...........NESTED LOOPS (cr=381 pr=0 pw=0 time=0.04)
|           44 ............HASH JOIN (cr=376 pr=0 pw=0 time=0.04)
|           44 .............TABLE ACCESS FULL OBJ# UNDO$ (cr=12 pr=0 pw=0 time=0.00)
|           44 .............TABLE ACCESS FULL OBJ# SEG$ (cr=364 pr=0 pw=0 time=0.03)
|           44 ............INDEX UNIQUE SCAN OBJ# I_FILE2 (cr=5 pr=0 pw=0 time=0.00)
|           44 ...........TABLE ACCESS CLUSTER OBJ# TS$ (cr=93 pr=0 pw=0 time=0.00)
|           44 ............INDEX UNIQUE SCAN OBJ# I_TS# (cr=5 pr=0 pw=0 time=0.00)
|           44 ..........TABLE ACCESS CLUSTER OBJ# USER$ (cr=93 pr=0 pw=0 time=0.00)
|           44 ...........INDEX UNIQUE SCAN OBJ# I_USER# (cr=5 pr=0 pw=0 time=0.00)
|            8 .........NESTED LOOPS (cr=419 pr=0 pw=0 time=0.03)
|            8 ..........NESTED LOOPS (cr=398 pr=0 pw=0 time=0.03)
|            8 ...........NESTED LOOPS (cr=377 pr=0 pw=0 time=0.03)
|            8 ............TABLE ACCESS FULL OBJ# SEG$ (cr=364 pr=0 pw=0 time=0.03)
|            8 ............TABLE ACCESS BY INDEX ROWID OBJ# FILE$ (cr=13 pr=0 pw=0 time=0.00)
|            8 .............INDEX UNIQUE SCAN OBJ# I_FILE2 (cr=5 pr=0 pw=0 time=0.00)
|            8 ...........TABLE ACCESS CLUSTER OBJ# TS$ (cr=21 pr=0 pw=0 time=0.00)
|            8 ............INDEX UNIQUE SCAN OBJ# I_TS# (cr=5 pr=0 pw=0 time=0.00)
|            8 ..........TABLE ACCESS CLUSTER OBJ# USER$ (cr=21 pr=0 pw=0 time=0.00)
|            8 ...........INDEX UNIQUE SCAN OBJ# I_USER# (cr=5 pr=0 pw=0 time=0.00)
|        42799 .......FIXED TABLE FIXED INDEX X$KTFBUE DEFSUBPARTLOB$ (cr=50408 pr=11504 pw=0 time=163.26)
|            0 ......TABLE ACCESS BY INDEX ROWID OBJ# FILE$ (cr=765 pr=0 pw=0 time=0.50)
|          759 .......INDEX UNIQUE SCAN OBJ# I_FILE1 (cr=6 pr=0 pw=0 time=0.28)
|            0 ..FIXED TABLE FULL X$LE I_RG#
Et elle etait censée mesurer quelques perfs ;-) !
Copyright © 1998-2002 Radu Caulea, TAFORA MAJ 06/11/2006 !
Commentaires et suggestions radu[CHEZ]tafora.fr